import MySQLdb
import random
# import md5 #Deprecated?
import hashlib

mysql_con = MySQLdb.Connect(host="localhost",user="root",passwd="root")

def createdb():
    cur = mysql_con.cursor()
    cur.execute("CREATE DATABASE tmp123")
    mysql_con.commit()
    cur.close()

def createtable():
    cur = mysql_con.cursor()
    cur.execute("USE tmp123")
    cur.execute("CREATE TABLE usertest (`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,`username` CHAR(32) NOT NULL,`passwd` CHAR(32) NOT NULL,`email` CHAR(100) NOT NULL,`hash` CHAR(32) NOT NULL,PRIMARY KEY (`id`)  )")
    mysql_con.commit()
    cur.close()

def insertrows():
    cur = mysql_con.cursor()
    for i in range(1,90001):
        strpass = str(random.randint(111,999))
        strhash = hashlib.md5("example"+str(i)+strpass+"example"+str(i)+"@example.com").hexdigest()
        cur.execute("INSERT INTO usertest (`id`,`username`,`passwd`,`email`,`hash`) VALUES (NULL,'example"+str(i)+"','"+strpass+"','example"+str(i)+"@example.com','"+strhash+"')")
    mysql_con.commit()
    cur.close()

def selectrows():
    cur = mysql_con.cursor(MySQLdb.cursors.DictCursor)
    cur.execute("SELECT * FROM usertest")
    for row in cur.fetchall():
        hashnew = hashlib.md5(row["username"]+row["passwd"]+row["email"]).hexdigest()
        if hashnew != row["hash"]:
            print "Something went wrong!"
    mysql_con.commit()
    cur.close()

def deleterows():
    cur = mysql_con.cursor()
    cur.execute("DELETE FROM usertest")
    mysql_con.commit()
    cur.close()

def deletetable():
    cur = mysql_con.cursor()
    cur.execute("DROP TABLE usertest")
    mysql_con.commit()
    cur.close()

def deletedb():
    cur = mysql_con.cursor()
    cur.execute("DROP DATABASE tmp123")
    mysql_con.commit()
    cur.close()

createdb()
createtable()
insertrows()
selectrows()
deleterows()
deletetable()
deletedb()